#!powershell
# This file is part of Ansible
#
# Ansible is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# Ansible is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with Ansible.  If not, see <http://www.gnu.org/licenses/>.

# WANT_JSON
# POWERSHELL_COMMON

<#
.SYNOPSIS
Create a database connection Object from a XML source

.DESCRIPTION
Transfrom a xml element to a database Object.
The XML Element must have a well defined provider value according the .Net specifications
and must have also a valid connectionString

.PARAMETER source
Required. the xml element which contains the database connection defintion
#>
Function New-DatabaseConnection
{
        Param( 
        [Parameter(Mandatory=$true,ValueFromPipeline=$false)] 
        [System.String]
        $connectionString,

        [Parameter(Mandatory=$true,ValueFromPipeline=$false)] 
        [System.String]
        $provider
        )

    Begin
    {
        $watch = [system.diagnostics.stopwatch]::StartNew() 
        write-verbose "Start New-DatabaseConnection for $connectionString and provider $provider"
    }
    End
    {
        write-verbose "End New-DatabaseConnection for $connectionString and provider $provider"
        $watch.Stop();
        Write-Verbose "New-DatabaseConnection duration : $($watch.Elapsed)"
    }
    Process 
    {

        #Add mandatory properties
        $newObj = New-Object System.Object       
        $newObj | Add-Member -type NoteProperty -name connectionString -value $connectionString
        $newObj | Add-Member -type NoteProperty -name provider -value $provider

        #Check that provider exists
        $factories = [System.Data.Common.DbProviderFactories]::GetFactoryClasses()
        $factory = $factories.Rows.Find($provider)
        if ($factory -eq $null)
        {
          throw New-Object Exception "Provider '$provider' is not installed on this computer"
        }

        #Add script property to obtain the database connection
        $newObj | Add-Member -MemberType ScriptProperty -Name DbFactory -Value {

            $f = [System.Data.Common.DbProviderFactories]::GetFactory($this.provider)
            Write-Output $f           

        }

        #TODO : Add check connection definition

        Write-Output $newObj
    }
}


<#
.SYNOPSIS
Execute a ExecuteReader query

.DESCRIPTION
Use this cmdlet to execute select query on the databse and retrieve a Powershell object in the pipeline.
Name of property is the name of the column query. DbNull become a $null value

.PARAMETER DatabaseConnection
Required. The powershell database connection object (Not DbConnection of the framework.Net). The DBConnection si provided by this Db Factory property of this object
.PARAMETER query
Required. The sql query to execute
#>
function Get-DatabaseData {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
        [PSOBject]
        $DatabaseConnection,

        [Parameter(Mandatory=$true,ValueFromPipeline=$false)]
        [string]$query
    )
    Begin
    {
        $watch = [system.diagnostics.stopwatch]::StartNew() 
        write-verbose "Start Get-DatabaseData from $DatabaseConnection"

    }
    End
    {
        write-verbose "End Get-DatabaseData from $DatabaseConnection"
        $watch.Stop();
        Write-Verbose "Function duration : $($watch.Elapsed)"
    }
    Process
    {
        #Prepare Database Access
        $cnx = $DatabaseConnection.DbFactory.CreateConnection()
        $cnx.ConnectionString = $DatabaseConnection.connectionString
        $cmd = $DatabaseConnection.DbFactory.CreateCommand()
        $cmd.CommandText = $query
        $cmd.Connection = $cnx
        Write-Verbose "Execute query : $query"

        #Open Database and read Data
        try
        {
            $cnx.Open()

            $reader = $cmd.ExecuteReader()
            while($reader.Read())
            {
                $newObj = New-Object PSObject

                for($i=0;$i -lt $reader.FieldCount;$i++)
                {
                    $columnName = $reader.GetName($i)
                    if ([System.String]::IsNullOrEmpty($columnName))
                    {
                        $columnName = "Col$i"
                    }

                    $dbValue = $reader.GetValue($i)
                    if ($dbValue -is [System.DBNull]) {$dbValue = $null}
                    $newObj | Add-Member -MemberType NoteProperty -Name $columnName -Value $dbValue
                }

                Write-Output $newObj
            }
            $reader.Close()
        }
        finally
        {
            #Free resources
            if ($cmd -ne $null)
            {
                $cmd.Dispose()
            }
        
        
            if ($cnx -ne $null)
            {
                if ($cnx.State -eq [System.Data.ConnectionState]::Open)
                {
                    $cnx.Close()
                }
                $cnx.Dispose()
            }

        }
    }
}



$params = Parse-Args $args $true;

$connectionString = Get-Attr $params "connectionString" -failifempty $true
$provider = Get-Attr $params "provider" -failifempty $true
$sqlquery = Get-Attr $params "sqlquery" -failifempty $false

$DatabaseConnection = New-DatabaseConnection -connectionString $connectionString -provider $provider

if ([String]::IsNullOrEmpty($sqlquery))
{
    #No sql query so we just open and close the connection
    $cnx = $DatabaseConnection.DbFactory.CreateConnection()
    $cnx.ConnectionString = $DatabaseConnection.connectionString
    try
    {

        $cnx.open()

    }
    catch [Exception]
    {
        Fail-Json $([String]::Format("Unable to connect database with connectionString '{0}' and provider '{1}'.\nError : {2}",  $connectionString, $provider, $_.message))
    }
    finally
    {
        if ($cnx -ne $null)
        {
            if ($cnx.State -eq [System.Data.ConnectionState]::Open)
            {
                $cnx.Close()
            }
            $cnx.Dispose()
        }

    }
}
else
{
    try
    {    
        $data = Get-DatabaseData -DatabaseConnection $DatabaseConnection -query $sqlquery
    }
    catch [Exception]
    {
        Fail-Json $([String]::Format("Unable to connect database with connectionString '{0}' and provider '{1}'.\nError : {2}",  $connectionString, $provider, $_.message))
    }

}

$result = New-Object psobject @{
    changed = $false
    data = [String]::Format("Connect database with connectionString '{0}' and provider '{1}' is OK",  $connectionString, $provider)
};

Exit-Json $result;
